﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data.SqlClient;
using System.Data;

namespace BLL
{
    public class CANLAMSANGBLL
    {
        DataService DB = new DataService();

        public void insert(int maloai, string ketquacls, int mabenhan, DateTime ngaytao, string file_patch)
        {
            string sql = "insert into canlamsang values (@maloai,@ketquacls,@mabenhan,@ngaytao,@file_patch)";

            SqlParameter paraMaloai = new SqlParameter("maloai", maloai);
            SqlParameter paraKq = new SqlParameter("ketquacls", ketquacls);
            SqlParameter paraMabenhan = new SqlParameter("mabenhan", mabenhan);
            SqlParameter paraNgaytao = new SqlParameter("ngaytao", ngaytao);
            SqlParameter paraFile = new SqlParameter("file_patch", file_patch);

            DB.ExecuteNonQuery(sql, paraMaloai, paraKq, paraMabenhan, paraNgaytao, paraFile);
        }

        public void update(int macanlamsang, int maloai, string ketquacls, int mabenhan, DateTime ngaytao, string file_patch)
        {
            string sql = "update canlamsang set maloai = '" + maloai.ToString() + ", ketquacls = '" + ketquacls + "', mabenhan = '" + mabenhan.ToString() + ", ngaytao = '" + ngaytao.ToString() + ", file_patch = '" + file_patch + "' where maloai = " + maloai.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int macanlamsang)
        {
            string sql = "delete from canlamsang where macanlamsang = " + macanlamsang.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public CANLAMSANG getCanlamsangWithIDLoaiClsAnIDHsba(int idLoaiCls, int hsba) {
            string sql = "select cls.* from canlamsang cls, loaicls lcls , hosobenhan hsba "
                        + " where cls.maloai = lcls.maloai and hsba.mabenhan = cls.mabenhan "
                        + " and cls.maloai = " + idLoaiCls + " and hsba.mabenhan = " + hsba;
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            CANLAMSANG cls = new CANLAMSANG();
            DataRow r = table.Rows[0];

            cls.macanlamsang = Int32.Parse( r[0].ToString());
            cls.maloai = Int32.Parse( r[1].ToString());
            cls.ketquacls = r[2].ToString();
            cls.mabenhan = Int32.Parse( r[3].ToString());
            cls.ngaytao = Convert.ToDateTime( r[4].ToString());
            try { cls.file_patch = r[5].ToString(); }
            catch { cls.file_patch = "";}

            return cls;
        }

        public void updateKetluanWithIDClamsang(int idCanlamsang, string ketluancls) {
            string sql = "update canlamsang set ketquacls = N'" + ketluancls + "' where macanlamsang = " + idCanlamsang.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void updateKetluanWithIDHsbaAndLcls(int mahsba, int malcls, string ketluancls)
        {
            string sql = "update canlamsang set ketquacls = '" + ketluancls + "' where mabenhan = " + mahsba.ToString() + " and maloai = " + malcls.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public CANLAMSANG getCanlamsangWithIDMabaAndIDLoaiCLS(int mabenhan, int maloai) {
            string sql = "select cls.macanlamsang, cls.maloai, cls.ketquacls, cls.mabenhan, cls.ngaytao, cls.file_patch "
                        + " from hosobenhan hsba, canlamsang cls, loaicls lcls "
                        + " where hsba.mabenhan = cls.mabenhan and cls.maloai = lcls.maloai "
                        + " and hsba.mabenhan = " + mabenhan + " and lcls.maloai = " + maloai;

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            DataRow r = table.Rows[0];
            CANLAMSANG cls = new CANLAMSANG();

            cls.macanlamsang = Int32.Parse(r[0].ToString());
            cls.maloai = Int32.Parse(r[1].ToString());
            cls.ketquacls = r[2].ToString();
            cls.mabenhan = Int32.Parse(r[3].ToString());
            cls.ngaytao = Convert.ToDateTime(r[4].ToString());
            try { cls.file_patch = r[5].ToString(); }
            catch { cls.file_patch = ""; }

            return cls;
        }
    }
}
